package cn.kgb.dao;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import cn.kgb.javabean.MailBox;
import cn.kgb.javabean.User;
import cn.kgb.util.DataAccess;

public class UserDAO {
	
	public List getAllUsers(){
		List list = new ArrayList();
		Connection con = DataAccess.getConnection();
		String sql = "SELECT * FROM `myjavamail_username`";
		Statement stmt = null;
		try {
			stmt = con.createStatement();
			ResultSet rs = stmt.executeQuery(sql);
	         while (rs.next()){
	         User user= new User();
	        
	         user.setId(rs.getInt("id"));
	         user.setUsername(rs.getString("username"));
	         user.setPassword(rs.getString("password"));
	         user.setNikename(rs.getString("nikename"));
	         user.setPhone(rs.getString("phone"));
	         user.setEmail(rs.getString("email"));
	         user.setServices(rs.getString("services"));
	         user.setLevel(rs.getInt("level"));
	         list.add(user);
	         
	         }
	         rs.close();
	         stmt.close();
	         con.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
        
         return list;
	}
	
	public List getServiceTypeUsers(String ServiceType){
		List list = new ArrayList();
		Connection con = DataAccess.getConnection();
		String sql = "SELECT * FROM `myjavamail_username` where services like'%"+ServiceType+"%' or services like'%all%'";
		Statement stmt = null;
		try {
			stmt = con.createStatement();
			ResultSet rs = stmt.executeQuery(sql);
	         while (rs.next()){
	         User user= new User();
	        
	         user.setId(rs.getInt("id"));
	         user.setUsername(rs.getString("username"));
	         user.setPassword(rs.getString("password"));
	         user.setNikename(rs.getString("nikename"));
	         user.setPhone(rs.getString("phone"));
	         user.setEmail(rs.getString("email"));
	         user.setServices(rs.getString("services"));
	         user.setLevel(rs.getInt("level"));
	         list.add(user);
	         
	         }
	         rs.close();
	         stmt.close();
	         con.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
        
         return list;
	}
	public User getUserById(String id){
		User user = new User();
		Connection con = DataAccess.getConnection();
		String sql = "SELECT * FROM `myjavamail_username` WHERE id = "+ id;
		Statement stmt = null;
		try {
			stmt = con.createStatement();
			ResultSet rs = stmt.executeQuery(sql);
	         while (rs.next()){
	        
	         user.setId(rs.getInt("id"));
	         user.setUsername(rs.getString("username"));
	         user.setPassword(rs.getString("password"));
	         user.setNikename(rs.getString("nikename"));
	         user.setPhone(rs.getString("phone"));
	         user.setEmail(rs.getString("email"));
	         user.setServices(rs.getString("services"));
	         user.setLevel(rs.getInt("level"));
	         
	         }
	         rs.close();
	         stmt.close();
	         con.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
        
         return user;
	}
	
	public List getUserByIds(String[] id){
		
		List list = new ArrayList();
		Connection con = DataAccess.getConnection();
		String sql = "";
		Statement stmt = null;
		ResultSet rs = null;
		try {
			stmt = con.createStatement();
			for(int i=0;i<id.length;i++){
				sql = "SELECT * FROM `myjavamail_username` WHERE id = "+ id[i];
				rs = stmt.executeQuery(sql);
				User user = new User();
		         while (rs.next()){
		         
		         user.setId(rs.getInt("id"));
		         user.setUsername(rs.getString("username"));
		         user.setPassword(rs.getString("password"));
		         user.setNikename(rs.getString("nikename"));
		         user.setPhone(rs.getString("phone"));
		         user.setEmail(rs.getString("email"));
		         user.setServices(rs.getString("services"));
		         user.setLevel(rs.getInt("level"));
		         
		         }
		         list.add(user);
			}
			
	         rs.close();
	         stmt.close();
	         con.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
        
         return list;
	}
	
	public User getUserByUsername(String username){
		User user = null;
		Connection con = DataAccess.getConnection();
		String sql = "SELECT * FROM `myjavamail_username` WHERE username = '"+ username +"'";
		Statement stmt = null;
		try {
			stmt = con.createStatement();
			ResultSet rs = stmt.executeQuery(sql);
	         while (rs.next()){
	         user = new User();
	         user.setId(rs.getInt("id"));
	         user.setUsername(rs.getString("username"));
	         user.setPassword(rs.getString("password"));
	         user.setNikename(rs.getString("nikename"));
	         user.setPhone(rs.getString("phone"));
	         user.setEmail(rs.getString("email"));
	         user.setServices(rs.getString("services"));
	         user.setLevel(rs.getInt("level"));
	         
	         }
	         rs.close();
	         stmt.close();
	         con.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
        
         return user;
	}
	public void deleteById(String[] id){
		Connection con = DataAccess.getConnection();
		Statement stmt = null;
		try {
			stmt = con.createStatement();
		} catch (SQLException e1) {
			// TODO Auto-generated catch block
			e1.printStackTrace();
		}
		int length = id.length;
		for(int i=0;i<length;i++){
			String sql = "DELETE FROM myjavamail_username WHERE id="+id[i];
			
			try {
				stmt.executeUpdate(sql);
			}catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		try {
			stmt.close();
			con.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			if (stmt != null) {
				try {
					stmt.close();
				} catch (Exception sqlex) {
				}
				stmt = null;
			}
			if (con != null) {
				try {
					con.close();
				} catch (Exception sqlex) {
				}
				con = null;
			}
		}
	}
	
	public void InsertUser(User user){
		Connection con = DataAccess.getConnection();
		
		String sql = "INSERT INTO myjavamail_username VALUES (NULL,'"+user.getUsername()+"','"+user.getNikename()+
		"','"+user.getPhone()+"','"+user.getEmail()+"','"+user.getServices()+"','"+user.getPassword()+"',"+user.getLevel()+")";
		System.out.println("sql:"+sql);
		Statement stmt = null;
		 try {
			stmt = con.createStatement();
			stmt.executeUpdate(sql);
			stmt.close();
			con.close(); 
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			
			if (stmt != null) {
				try {
					stmt.close();
				} catch (Exception sqlex) {
				}
				stmt = null;
			}
			if (con != null) {
				try {
					con.close();
				} catch (Exception sqlex) {
				}
				con = null;
			}
		}
	}
	
	public void modifyById(User user){
		Connection con = DataAccess.getConnection();
		
		String sql = "UPDATE myjavamail_username set username='"+user.getUsername()+"',password='"+user.getPassword()+"',nikename='"+user.getNikename()+
		"',phone='"+user.getPhone()+"',email='"+user.getEmail()+"',services='"+user.getServices()+"',level="+
		user.getLevel()+" WHERE id ="+user.getId();
		System.out.println("sql:"+sql);
		Statement stmt = null;
		 try {
			stmt = con.createStatement();
			stmt.executeUpdate(sql);
			stmt.close();
			con.close(); 
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			
			if (stmt != null) {
				try {
					stmt.close();
				} catch (Exception sqlex) {
				}
				stmt = null;
			}
			if (con != null) {
				try {
					con.close();
				} catch (Exception sqlex) {
				}
				con = null;
			}
		}
	}
}
